Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Temp-table methods

You define every aspect of a dynamic temp-table through its methods. Using these methods, you can define it to be like another table, to inherit individual field definitions from another table, or to create fields that have no relationship to fields in other tables. You can also define one or more indexes for the temp-table. You can execute a number of these methods in sequence to build up a temp-table that you then finalize and use. All the methods return a logical success flag. You should check this flag if it is possible that the arguments to the method might be invalid or that the method might fail for some other reason.

CREATE-LIKE method

If you want to create a temp-table LIKE a database table or another temp-table with all its fields and at least one of its indexes, use this method to copy all fields from the source table to the temp-table definition, along with index definitions:

tt-handle:CREATE-LIKE( { source-handle-exp | source-name-exp }     [, source-index-name-exp ] ).

You define the source table by either passing its handle or its name. The name expression can be either the table name as a quoted literal or a character expression that evaluates to the table name. A source table can be either a database table for a currently connected database or another temp-table whose scope makes it available to the procedure with the CREATE-LIKE method.

If you do not pass the optional second argument, then the temp-table inherits all the index definitions of the underlying table. If you want to specify only one index from the underlying table initially, you can pass its name as a character expression as the second argument. For example, this sequence of statements creates a dynamic temp-table with all the fields from the Customer table, along with just the Name index:

DEFINE VARIABLE hTT AS HANDLE     NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name").

You can only use the CREATE-LIKE method once for a temp-table definition, when the temp-table is in the clear state. Thus, if you use this method, it must be the first method you invoke for the temp-table. If you want fields from additional tables in the temp-table, you use the ADD-FIELDS-FROM method to add them. If you want to add more indexes from the source table, you can use the ADD-LIKE-INDEX method to do this.

ADD-FIELDS-FROM method

If you want to add a subset of fields from another table to a temp-table, you use the ADD-FIELDS-FROM method:

tt-handle:ADD-FIELDS-FROM( { source-handle-exp | source-name-exp}     [, except-list-exp ] ).

You can also use this method as many times as you need to in order to add fields from one or more additional tables to a temp-table that you’ve already started to build using the CREATE-LIKE or another ADD-FIELDS-FROM method.

As with the CREATE-LIKE method, you pass either the source table handle or an expression representing its name. If you want to exclude some fields from being copied into the temp-table definition, pass a comma-separated list of these field names as the optional second argument. If you add a field whose name is already in the target temp-table, Progress ignores the duplicate field and does not add it to the temp-table.

This example adds all fields from the SalesRep table except the MonthQuota and Region fields to the fields from the Customer table already in the temp-table:

DEFINE VARIABLE hTT AS HANDLE NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota, Region").

Both the Customer and SalesRep tables have a SalesRep field with the SalesRep’s initials. Because this field is already in the temp-table from the Customer table, it is not added from the SalesRep table, and no error results.

ADD-LIKE-FIELD method

If you want to add fields from another table to the temp-table individually, or you need to rename fields as you add them, use the ADD-LIKE-FIELD method once for each field:

tt-handle:ADD-LIKE-FIELD( tt-field-name-exp ,     { source-buffer-field-handle-exp | source-db-field-name-exp } ).

This method takes two arguments:

The name of the field in the temp-table, as a character expression.
An identifier of the source field, which can be either its buffer-field handle or an expression evaluating to its name, with the table name qualifier (and, if necessary, its database name qualifier).

This example adds the Region field from the SalesRep table, which was not added in the ADD-FIELDS-FROM method, and renames it to Area:

DEFINE VARIABLE hTT AS HANDLE     NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region").

ADD-NEW-FIELD method

If you want to add one or more fields to a temp-table definition that are not derived from a specific other database or temp-table field name, use the ADD-NEW-FIELD method:

tt-handle:ADD-NEW-FIELD( tt-field-name-exp , data type-exp [ , extent-exp [, format-exp [, initial-exp [, label-exp [, column-label-exp] ] ] ] ] ).

The first argument is the field name in the temp-table. The second is its data type. These two arguments are required. You can also define other attributes optionally, in this order:

The extent of the field, if it has one.
The format of the field.
The field’s initial value.
The field’s label.
The field’s column-label.

Because these arguments are position-dependent, you must include values for any intervening arguments you don’t specify. You don’t need to include commas or values for optional arguments following the last one you specify. For example, this statement adds an integer field called Sequence to the temp-table, with a format of "9999" and an initial value of 1000:

DEFINE VARIABLE hTT AS HANDLE      NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000).

ADD-LIKE-INDEX method

You’ve already seen how a temp-table can inherit either one or all of the indexes from an underlying table, in the CREATE-LIKE method. You can also add indexes one at a time using other methods. The first of these is ADD-LIKE-INDEX. This method adds a single index to the temp-table that is derived from an existing index on another table. You specify the name you want the index to have in the temp-table, the name of the index in the source table, and either the buffer-handle to the source table or an expression holding its name:

tt-handle:ADD-LIKE-INDEX( tt-index-name-exp , source-index-name-exp     {, source-buffer-handle-exp | source-db-table-name-exp } ).

This example adds the CustNum index to the temp-table, in addition to the Name index that was added in the CREATE-LIKE method:

DEFINE VARIABLE hTT AS HANDLE NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000). hTT:ADD-LIKE-INDEX("CustNum","CustNum","Customer").

ADD-NEW-INDEX method

If you want to add an index to a temp-table that isn’t derived from an index in another table, use the ADD-NEW-INDEX method:

tt-handle:ADD-NEW-INDEX( index-name-exp [, is-unique [, is-primary     [, is-word-index ] ] ] ).

You supply the index name and up to three optional logical values that indicate whether the new index has enforcement of unique values, whether it is the primary index of the temp-table, and whether it is a word-index. (A word index is a special index type on a CHARACTER field that allows Progress to retrieve records based on any word the field contains.)

ADD-INDEX-FIELD method

After you use ADD-NEW-INDEX, you invoke the ADD-INDEX-FIELD method once for each field in the new index. You pass the name of the index, the name of the field to add, and an optional third argument that evaluates to asc for ascending (the default) or desc for descending:

tt-handle:ADD-INDEX-FIELD( index-name-exp ,field-name-exp [ , mode-exp ] ).

If there are multiple fields in the index, the order in which you invoke ADD-INDEX-FIELD for the fields determines their order within the index.

This example adds a new unique index called SeqIndex to the temp-table and adds the Sequence field to it:

DEFINE VARIABLE hTT AS HANDLE      NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000). hTT:ADD-LIKE-INDEX("CustNum","CustNum","Customer"). hTT:ADD-NEW-INDEX("SeqIndex", YES). hTT:ADD-INDEX-FIELD("SeqIndex", "Sequence").

TEMP-TABLE-PREPARE method

Once you have invoked all the methods that you need to fully define the temp-table fields and indexes, you must use the TEMP-TABLE-PREPARE method to finalize the definition before you use the temp-table. You must pass an argument to the method that gives the temp-table a name, as in this example:

DEFINE VARIABLE hTT AS HANDLE      NO-UNDO. CREATE TEMP-TABLE hTT. hTT:CREATE-LIKE("Customer","Name"). hTT:ADD-FIELDS-FROM("SalesRep","MonthQuota"). hTT:ADD-LIKE-FIELD("Area", "SalesRep.Region"). hTT:ADD-NEW-FIELD("Sequence", "INTEGER",0,"9999",1000). hTT:ADD-LIKE-INDEX("CustNum","CustNum","Customer"). hTT:ADD-NEW-INDEX("SeqIndex", YES). hTT:ADD-INDEX-FIELD("SeqIndex", "Sequence"). hTT:TEMP-TABLE-PREPARE("CustSequence").

Once you have invoked this method, the temp-table is in the prepared state. You can now begin to use it, creating and deleting records in the temp-table, opening queries on the table, and so forth. You cannot use the temp-table in any way until you prepare it. After you use the TEMP-TABLE-PREPARE method, you cannot invoke any of the other methods to change its definition except the CLEAR method discussed next.

CLEAR method

If you want to reuse a temp-table handle for a different temp-table definition, you can invoke the CLEAR method on the handle. The CLEAR method takes no arguments. It empties the temp-table if there are any records in it and completely erases its definition, returning the handle to the clear state. At this point, you can begin to build up a definition again from scratch. There is no way to undo parts of a dynamic temp-table definition, and there is no way to extend its list of fields and indexes once it has been prepared.